<?php
/**
 * Created by PhpStorm.
 * User: hp
 * Date: 2017/11/21
 * Time: 8:59
 */

namespace App\Modules\Channel\Models;

use Illuminate\Database\Eloquent\Model;
use DB;

class Channel extends Model
{
    protected $table = 'distribution_channels';
    protected $fillable = ['id', 'name', 'pay_merchant_id', 'phone', 'nickname', 'person_in_charge_name', 'latest_login_time', 'remark', 'latest_login_ip', 'password', 'register_ip', 'is_enabled', 'distribution_manages_id', 'channel_user_id', 'site_nick_name', 'is_outer_site', 'is_old_user_site', 'is_yq_move'];

    static function getChannelUserIdById($id)
    {
        return self::select('channel_user_id')->where('id', $id)->first();
    }

    static function getIdById($id)
    {
        return self::where('id', $id)->first();
    }

    static function getChannelNicknameById($id)
    {
        return self::where('id', $id)->select('nickname')->first();
    }

    static function getChannelChargeData($params = [], $is_all = false)
    {
        $search_obj = self::leftjoin('channel_users', 'channel_users.id', '=', 'distribution_channels.channel_user_id')
            ->leftjoin('order_day_stats', 'distribution_channels.id', '=', 'order_day_stats.distribution_channel_id');

        $search_obj->select(
            'channel_users.nickname as channel_user_nickname',
            'distribution_channels.nickname',
            'distribution_channels.site_nick_name',
            'channel_users.id',
            'channel_users.phone',
            'channel_users.created_at',
            'order_day_stats.distribution_channel_id',
            DB::raw('sum(total_recharge_amount) charge_amount_total'));

        if (isset($params['distribution_channel_ids']) && $params['distribution_channel_ids']) {
            $search_obj->whereIn('distribution_channels.id', $params['distribution_channel_ids']);
        }

        if (isset($params['groupBy']) && $params['groupBy']) {
            $groupBy = '';
            if ($params['groupBy'] == 'account') {
                $groupBy = 'channel_users.id';
            } else if ($params['groupBy'] == 'site') {
                $groupBy = 'distribution_channels.id';
            }
            $search_obj->groupBy($groupBy);
        }

        \Log::info($search_obj->toSql());
        if ($is_all) {
            return $search_obj->get();
        } else {
            return $search_obj->paginate();
        }
    }

    static function getChannelDayData($params = [], $is_all)
    {
        $search_obj = self::leftjoin('order_day_stats', 'distribution_channels.id', '=', 'order_day_stats.distribution_channel_id')
            ->leftjoin('channel_users', 'channel_users.id', '=', 'distribution_channels.channel_user_id')
            ->select('date',
                'channel_users.phone',
                'channel_users.nickname as channel_user_nickname',
                'distribution_channels.nickname',
                'distribution_channels.site_nick_name',
                'order_day_stats.distribution_channel_id',
                DB::raw('sum(total_recharge_amount) total_recharge_amount'),
                DB::raw('sum(pay_success_user_num) pay_success_user_num'),
                DB::raw('sum(year_recharge_user_num) year_recharge_user_num'),
                DB::raw('sum(paid_num) paid_num'),
                DB::raw('sum(register_user_num) register_user_num'),
                DB::raw('sum(subscribe_user_num) subscribe_user_num'),
                DB::raw('sum(old_user_subscribe_num) old_user_subscribe_num'),
                DB::raw('sum(reg_user_first_recharge_amount) reg_user_first_recharge_amount'));
        $search_obj->orderBy('date', 'desc');

        if (isset($params['month']) && $params['month']) $search_obj->where('month', $params['month']);
        if (isset($params['distribution_channel_id']) && $params['distribution_channel_id']) $search_obj->where('order_day_stats.distribution_channel_id', $params['distribution_channel_id']);
        if (isset($params['distribution_channel_ids']) && $params['distribution_channel_ids']) $search_obj->whereIn('order_day_stats.distribution_channel_id', $params['distribution_channel_ids']);
        if (isset($params['distribution_channel_name']) && $params['distribution_channel_name']) $search_obj->where('order_day_stats.distribution_channel_name', $params['distribution_channel_name']);
        if (isset($params['date']) && $params['date']) $search_obj->where('date', $params['date']);

        $end_date = date('Y-m-d', strtotime('-1 day'));
        $start_date = date('Y-m-d', strtotime("-1 month"));;

        if (isset($params['end_date']) && $params['end_date']) {
            $end_date = $params['end_date'];
        }

        if (isset($params['start_date']) && $params['start_date']) {
            $start_date = $params['start_date'];
        }

        $search_obj->where('date', '>=', $start_date)->where('date', '<=', $end_date);
        if (isset($params['groupBy']) && $params['groupBy']) {
            if ($params['groupBy'] == 'site') {
                $search_obj->groupBy('order_day_stats.distribution_channel_id');
            } elseif ($params['groupBy'] == 'account') {
                $search_obj->groupBy('channel_users.id');
            }
        }
        $search_obj->groupBy('date');
        \Log::info($search_obj->toSql());
        if ($is_all) {
            return $search_obj->get();
        } else {
            return $search_obj->paginate();
        }
    }

    static public function getChannelFansData($params = [], $isAll = false)
    {
        $search_obj = self::join('channel_users', 'channel_users.id', '=', 'distribution_channels.channel_user_id')
            ->join('official_accounts', 'distribution_channels.id', '=', 'official_accounts.distribution_channel_id')
            ->join('user_subscribe_statistics', 'user_subscribe_statistics.appid', '=', 'official_accounts.appid');

        $search_obj->orderBy('date', 'desc');
        $search_obj->groupBy('date');
        $fields = [
            DB::raw("date,
                     channel_users.phone,
                     distribution_channels.nickname,
                     channel_users.nickname as channel_user_nickname,
                     distribution_channels.site_nick_name,
                     official_accounts.distribution_channel_id,
                     sum(one_day_recharge) one_day_recharge,
                     sum(three_days_recharge) three_days_recharge,
                     sum(seven_days_recharge) seven_days_recharge,
                     sum(thirty_days_recharge) thirty_days_recharge,
                     sum(sixty_days_recharge) sixty_days_recharge,
                     sum(new_sub_recharge_num) new_sub_recharge_num,
                     sum(recharge_sum) new_sub_user_recharge_amount,
                     sum(new_subscribe_num) new_subscribe_num,
                     sum(actual_subscribe_num) actual_subscribe_num,
                     sum(sub_user_orders_num) sub_user_orders_num          
                     ")
        ];

        $search_obj->select($fields);
        if (isset($params['distribution_channel_ids']) && $params['distribution_channel_ids']) {
            $search_obj->whereIn('official_accounts.distribution_channel_id', $params['distribution_channel_ids']);
        }

        if (isset($params['distribution_channel_id']) && $params['distribution_channel_id']) {
            $search_obj->where('official_accounts.distribution_channel_id', $params['distribution_channel_id']);
        }

        if (isset($params['date']) && $params['date']) {
            $search_obj->where('user_subscribe_statistics.date', $params['date']);
        }

        if (isset($params['end_date']) && $params['end_date']) {
            $search_obj->where('user_subscribe_statistics.date', '<=', $params['end_date']);
        }

        if (isset($params['start_date']) && $params['start_date']) {
            $search_obj->where('user_subscribe_statistics.date', '>=', $params['start_date']);
        }

        if (isset($params['groupBy']) && $params['groupBy']) {
            if ($params['groupBy'] == 'site') {
                $params['groupBy'] = 'distribution_channels.id';
            } elseif ($params['groupBy'] == 'account') {
                $params['groupBy'] = 'channel_users.id';
            }
            $search_obj->groupBy($params['groupBy']);
        }
        \Log::info($search_obj->toSql());
        if ($isAll) {
            return $search_obj->get();
        } else {
            return $search_obj->paginate();
        }
    }
}